"""
发票报表视图
"""
from django.shortcuts import render


# 进入 报表模块—按天统计的界面，to_day_statistics_invoice
def to_day_statistics_invoice(request):
    return render(request, 'page/invoice/day_statistics_invoice.html')
# 按天统计报表数据接口api
from django.http import JsonResponse
from common.db_util import MySqlDb
from datetime import datetime
import calendar


# 按天统计报表数据接口api
def day_statistics_invoice(request):
    """
    按天统计发票数据接口
    查询参数：year_month (格式：YYYY-MM，默认当前月份)
    返回：按天统计的合计金额（不含税）、合计税额及价税合计
    """
    try:
        # 获取年月参数，默认当前月份
        year_month = request.GET.get('year_month', '')
        if not year_month:
            # 默认当前月份
            now = datetime.now()
            year_month = now.strftime('%Y-%m')
        
        # 解析年月
        year, month = year_month.split('-')
        year = int(year)
        month = int(month)
        
        # 获取当月天数
        _, days_in_month = calendar.monthrange(year, month)
        
        # 构建SQL查询语句（按天统计）
        sql = """
            SELECT 
                DAY(issue_date) as day,
                COALESCE(SUM(total_amount), 0) as total_amount,
                COALESCE(SUM(total_tax), 0) as total_tax,
                COALESCE(SUM(total_price_tax), 0) as total_price_tax
            FROM invoice
            WHERE YEAR(issue_date) = :year AND MONTH(issue_date) = :month
            GROUP BY DAY(issue_date)
            ORDER BY DAY(issue_date)
        """
        
        params = {'year': year, 'month': month}
        
        # 执行查询
        result = MySqlDb.query(sql, params)
        
        # 初始化完整的天数数据（1-30/31）
        day_data = {i: {'day': i, 'total_amount': 0, 'total_tax': 0, 'total_price_tax': 0} 
                    for i in range(1, days_in_month + 1)}
        
        # 填充实际查询到的数据
        for item in result:
            day = item['day']
            day_data[day] = {
                'day': day,
                'total_amount': float(item['total_amount']),
                'total_tax': float(item['total_tax']),
                'total_price_tax': float(item['total_price_tax'])
            }
        
        # 转换为列表并按天排序
        data_list = [day_data[i] for i in range(1, days_in_month + 1)]
        
        return JsonResponse({
            'status': 'success',
            'code': 200,
            'message': '查询成功',
            'data': {
                'year_month': year_month,
                'days_in_month': days_in_month,
                'statistics': data_list
            }
        })
        
    except ValueError as e:
        return JsonResponse({
            'status': 'error',
            'code': 400,
            'message': f'日期格式错误：{str(e)}'
        }, status=400)
    except Exception as e:
        print(f"按天统计发票数据失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'code': 500,
            'message': f'服务器错误：{str(e)}'
        }, status=500)


# 进入 报表模块—按月统计的界面，to_month_statistics_invoice
def to_month_statistics_invoice(request):
    return render(request, 'page/invoice/month_statistics_invoice.html')


# 按月统计报表数据接口api
def month_statistics_invoice(request):
    """
    按月统计发票数据接口
    查询参数：year (格式：YYYY，默认当前年份)
    返回：按月统计的合计金额（不含税）、合计税额及价税合计
    统计字段：基于issue_date（开票日期）
    """
    try:
        # 获取年份参数，默认当前年份
        year = request.GET.get('year', '')
        if not year:
            # 默认当前年份
            now = datetime.now()
            year = now.year
        else:
            year = int(year)
        
        # 构建SQL查询语句（按月统计，使用issue_date字段）
        sql = """
            SELECT 
                MONTH(issue_date) as month,
                COALESCE(SUM(total_amount), 0) as total_amount,
                COALESCE(SUM(total_tax), 0) as total_tax,
                COALESCE(SUM(total_price_tax), 0) as total_price_tax
            FROM invoice
            WHERE YEAR(issue_date) = :year
            GROUP BY MONTH(issue_date)
            ORDER BY MONTH(issue_date)
        """
        
        params = {'year': year}
        
        # 执行查询
        result = MySqlDb.query(sql, params)
        
        # 初始化完整的12个月数据
        month_data = {i: {'month': i, 'total_amount': 0, 'total_tax': 0, 'total_price_tax': 0} 
                      for i in range(1, 13)}
        
        # 填充实际查询到的数据
        for item in result:
            month = item['month']
            month_data[month] = {
                'month': month,
                'total_amount': float(item['total_amount']),
                'total_tax': float(item['total_tax']),
                'total_price_tax': float(item['total_price_tax'])
            }
        
        # 转换为列表并按月排序
        data_list = [month_data[i] for i in range(1, 13)]
        
        return JsonResponse({
            'status': 'success',
            'code': 200,
            'message': '查询成功',
            'data': {
                'year': year,
                'statistics': data_list
            }
        })
        
    except ValueError as e:
        return JsonResponse({
            'status': 'error',
            'code': 400,
            'message': f'年份格式错误：{str(e)}'
        }, status=400)
    except Exception as e:
        print(f"按月统计发票数据失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'code': 500,
            'message': f'服务器错误：{str(e)}'
        }, status=500)


# 进入 报表模块—按季度统计的界面，to_season_statistics
def to_season_statistics(request):
    return render(request, 'page/invoice/season_statistics_invoice.html')


# 按季度统计报表数据接口api
def data_season_statistics(request):
    """
    按季度统计发票数据接口
    查询参数：year (格式：YYYY，默认当前年份)
    返回：按季度统计的合计金额（不含税）、合计税额及价税合计
    统计字段：基于issue_date（开票日期）
    季度定义：Q1(1-3月)、Q2(4-6月)、Q3(7-9月)、Q4(10-12月)
    """
    try:
        # 获取年份参数，默认当前年份
        year = request.GET.get('year', '')
        if not year:
            # 默认当前年份
            now = datetime.now()
            year = now.year
        else:
            year = int(year)
        
        # 构建SQL查询语句（按月统计，然后在应用层汇总为季度）
        sql = """
            SELECT 
                MONTH(issue_date) as month,
                COALESCE(SUM(total_amount), 0) as total_amount,
                COALESCE(SUM(total_tax), 0) as total_tax,
                COALESCE(SUM(total_price_tax), 0) as total_price_tax
            FROM invoice
            WHERE YEAR(issue_date) = :year
            GROUP BY MONTH(issue_date)
            ORDER BY MONTH(issue_date)
        """
        
        params = {'year': year}
        
        # 执行查询
        result = MySqlDb.query(sql, params)
        
        # 初始化12个月的数据
        month_data = {i: {'total_amount': 0, 'total_tax': 0, 'total_price_tax': 0} 
                      for i in range(1, 13)}
        
        # 填充实际查询到的数据
        for item in result:
            month = item['month']
            month_data[month] = {
                'total_amount': float(item['total_amount']),
                'total_tax': float(item['total_tax']),
                'total_price_tax': float(item['total_price_tax'])
            }
        
        # 按季度汇总数据
        quarters = {
            'Q1': {'quarter': 'Q1', 'name': '第一季度', 'total_amount': 0, 'total_tax': 0, 'total_price_tax': 0},
            'Q2': {'quarter': 'Q2', 'name': '第二季度', 'total_amount': 0, 'total_tax': 0, 'total_price_tax': 0},
            'Q3': {'quarter': 'Q3', 'name': '第三季度', 'total_amount': 0, 'total_tax': 0, 'total_price_tax': 0},
            'Q4': {'quarter': 'Q4', 'name': '第四季度', 'total_amount': 0, 'total_tax': 0, 'total_price_tax': 0}
        }
        
        # Q1: 1-3月, Q2: 4-6月, Q3: 7-9月, Q4: 10-12月
        quarter_months = {
            'Q1': [1, 2, 3],
            'Q2': [4, 5, 6],
            'Q3': [7, 8, 9],
            'Q4': [10, 11, 12]
        }
        
        # 汇总季度数据
        for quarter, months in quarter_months.items():
            for month in months:
                quarters[quarter]['total_amount'] += month_data[month]['total_amount']
                quarters[quarter]['total_tax'] += month_data[month]['total_tax']
                quarters[quarter]['total_price_tax'] += month_data[month]['total_price_tax']
        
        # 转换为列表（Q1-Q4顺序）
        data_list = [quarters['Q1'], quarters['Q2'], quarters['Q3'], quarters['Q4']]
        
        return JsonResponse({
            'status': 'success',
            'code': 200,
            'message': '查询成功',
            'data': {
                'year': year,
                'statistics': data_list
            }
        })
        
    except ValueError as e:
        return JsonResponse({
            'status': 'error',
            'code': 400,
            'message': f'年份格式错误：{str(e)}'
        }, status=400)
    except Exception as e:
        print(f"按季度统计发票数据失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'code': 500,
            'message': f'服务器错误：{str(e)}'
        }, status=500)

# 进入 报表模块—按年度统计的界面，to_year_statistics
def to_year_statistics(request):
    return render(request, 'page/invoice/year_statistics_invoice.html')


# 按年度统计报表数据接口api
def data_year_statistics(request):
    """
    按年度统计发票数据接口（柱状图样式）
    查询参数：year (格式：YYYY，默认当前年份)
    返回：近3年统计数据（year+1, year, year-1）
    每年汇总：合计金额（不含税）、合计税额及价税合计
    统计字段：基于issue_date（开票日期）
    """
    try:
        # 获取年份参数，默认当前年份
        year = request.GET.get('year', '')
        if not year:
            # 默认当前年份
            now = datetime.now()
            year = now.year
        else:
            year = int(year)
        
        # 计算近3年：year+1, year, year-1
        base_year = year
        years = [base_year + 1, base_year, base_year - 1]
        
        # 构建SQL查询语句（按年统计，使用issue_date字段）
        sql = """
            SELECT 
                YEAR(issue_date) as year,
                COALESCE(SUM(total_amount), 0) as total_amount,
                COALESCE(SUM(total_tax), 0) as total_tax,
                COALESCE(SUM(total_price_tax), 0) as total_price_tax
            FROM invoice
            WHERE YEAR(issue_date) IN (:year1, :year2, :year3)
            GROUP BY YEAR(issue_date)
            ORDER BY YEAR(issue_date)
        """
        
        params = {'year1': years[0], 'year2': years[1], 'year3': years[2]}
        
        # 执行查询
        result = MySqlDb.query(sql, params)
        
        # 初始化3年的数据
        year_data = {}
        for y in years:
            year_data[y] = {
                'year': y,
                'total_amount': 0,
                'total_tax': 0,
                'total_price_tax': 0
            }
        
        # 填充实际查询到的数据
        for item in result:
            year_key = item['year']
            if year_key in year_data:
                year_data[year_key] = {
                    'year': year_key,
                    'total_amount': float(item['total_amount']),
                    'total_tax': float(item['total_tax']),
                    'total_price_tax': float(item['total_price_tax'])
                }
        
        # 转换为列表并按年排序（year+1, year, year-1顺序）
        data_list = [year_data[y] for y in years]
        
        return JsonResponse({
            'status': 'success',
            'code': 200,
            'message': '查询成功',
            'data': {
                'base_year': base_year,
                'years': years,
                'statistics': data_list
            }
        })
        
    except ValueError as e:
        return JsonResponse({
            'status': 'error',
            'code': 400,
            'message': f'年份格式错误：{str(e)}'
        }, status=400)
    except Exception as e:
        print(f"按年度统计发票数据失败: {str(e)}")
        return JsonResponse({
            'status': 'error',
            'code': 500,
            'message': f'服务器错误：{str(e)}'
        }, status=500)